﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using MySql.Data.MySqlClient;
using System.Data;
namespace Agrond.Connection
{
    public class MySQL
    {
        public MySqlCommand MySQLCmd;
        public MySqlConnection MySQLCnn;
        public MySqlDataAdapter da;
        public DataSet ds = new DataSet();

        /*Connection String*/
        private string strConnectionString;
        public void setStrConnectionString(string pConnectionString)
        {
            strConnectionString = pConnectionString;
        }

        private int ConnectType(String connType)
        {
            try
            {
                if (connType == "MsSQL")
                    return 1;
                else if (connType == "MySQL")
                    return 2;
                else if (connType == "Access")
                    return 3;
                else return 0;
            }
            catch (Exception e) { Console.Write(e.ToString()); return 0; }
        }



        private void DisposeConnect()
        {
            MySQLCnn.Dispose();
            MySQLCnn.Close();
        }
        //This Function use to make connect to databse 
        private void ConnectMsSQL()
        {
            try
            {
                MySQLCnn = new MySqlConnection();
                MySQLCnn.ConnectionString = strConnectionString;
                MySQLCnn.Open();
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
                //show error
            }
        }

        /// <summary>
        /// This Function use to execute SQL scalar
        /// </summary>
        /// <param name="strConnect">Connection's String</param>
        /// <param name="strQuery">Query's String</param>
        /// <param name="connType">Type of conection number</param>
        /// <returns>this function return true if connection is executed successfully,It will return false</returns>
        public Boolean ExeMSSQLScalarQuery(String strQuery)
        {
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MySQLCmd = MySQLCnn.CreateCommand();
                MySQLCmd.CommandText = strQuery;
                MySQLCmd.ExecuteNonQuery();
                DisposeConnect();
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
                return false;
            }
            return true;
        }
        /*This Function use to give data form database to dataset
         * input:   Connection's String
         *          Query's String 
         *          Type of conection number
         *          Name of datatable
         * return: this function return dataset
         */
        public DataTable ReturnDataTable(String strQuery)
        {
            DataTable dtbl = new DataTable();
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MySQLCmd = MySQLCnn.CreateCommand();
                MySQLCmd.CommandText = strQuery;
                da = new MySqlDataAdapter(MySQLCmd);
                da.Fill(dtbl);
                da.Dispose();
                DisposeConnect();
                return dtbl;
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
            }
            return dtbl;
        }
        public Boolean check_dataNull(String strQuery)
        {
            DataTable dsToCheck = new DataTable();
            dsToCheck = ReturnDataTable(strQuery);
            if (dsToCheck.Rows.Count == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /*----------------------------------------------------------------------------*/
        //use for storeproceduce
        public Boolean ExeStoreproceduce(String storeProceduce,
                                        String[] ParameterName, String[] ParameterValue)
        {
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MySQLCmd = MySQLCnn.CreateCommand();
                MySQLCmd.CommandType = CommandType.StoredProcedure;
                MySQLCmd.CommandText = storeProceduce;
                for (int i = 0; i < ParameterName.Length; i++)
                {
                    MySQLCmd.Parameters.AddWithValue(ParameterName[i], ParameterValue[i]);
                }
                MySQLCmd.ExecuteNonQuery();
                DisposeConnect();
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
                return false;
            }
            return true;
        }
        public DataTable SPReturnDataTable(String storeProceduce,
                                                String ParameterName, String ParameterValue)
        {
            DataTable dtbl = new DataTable();
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MySQLCmd = MySQLCnn.CreateCommand();
                MySQLCmd.CommandText = storeProceduce;
                MySQLCmd.CommandType = CommandType.StoredProcedure;
                MySQLCmd.Parameters.AddWithValue(ParameterName, ParameterValue);
                da = new MySqlDataAdapter(MySQLCmd);
                da.Fill(dtbl);
                da.Dispose();
                DisposeConnect();
                return dtbl;
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
            }
            return dtbl;
        }
        public DataTable SPReturnDataTableMutilParameter(String storeProceduce,
                                                String[] ParameterName, String[] ParameterValue)
        {
            DataTable dtbl = new DataTable();
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MySQLCmd = MySQLCnn.CreateCommand();
                MySQLCmd.CommandText = storeProceduce;
                MySQLCmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < ParameterName.Length; i++)
                {
                    MySQLCmd.Parameters.AddWithValue(ParameterName[i], ParameterValue[i]);
                }
                da = new MySqlDataAdapter(MySQLCmd);
                da.Fill(dtbl);
                da.Dispose();
                DisposeConnect();
                return dtbl;
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
            }
            return dtbl;
        }
    }
}
